//
//  CDBManager.m
//  Fiverun
//
//  Created by yilong.zhang on 11-8-17.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "CDBManager.h"

@implementation CKey

@synthesize m_Name;
@synthesize m_Type;
@synthesize m_Value;
@synthesize m_nType;
@synthesize m_bPrimary;

-(id)initWithType:(int)type Length:(int)length Name:(NSString*)name Primary:(BOOL)primary
{
    self = [super init];
    if (self)
    {
        NSString *tmp = nil;
        if (1 == type)
        {
            self.m_Type = @"ld";
            tmp = [NSString stringWithFormat:@"integer(%d)", length];
        }
        else if (2 == type)
        {
            self.m_Type = @"f";
            tmp = [NSString stringWithFormat:@"float(%d)", length];
        }
        else if (3 == type)
        {
            self.m_Type = @"@";
            tmp = [NSString stringWithFormat:@"char(%d)", length];
        }
        else if (4 == type)
        {
            self.m_Type = @"blob";
            tmp = [NSString stringWithString:@"blob"];
        }
        else if (5 == type)
        {
            self.m_Type = @"NULL";
        }
        else {;}
        
        self.m_nType    = type;
        self.m_bPrimary = primary;
        if (self.m_bPrimary)
        {
            self.m_Name = [NSString stringWithFormat:@"%@ %@ NOT NULL UNIQUE PRIMARY KEY", name, tmp];
        }
        else 
        {
            self.m_Name = [NSString stringWithFormat:@"%@ %@", name, tmp];
        }
    }
    
    return self;
}

-(id)initWithType:(int)type Value:(id)value Name:(NSString*)name
{
    self = [super init];
    if (self)
    {
        if (1 == type)
        {
            self.m_Type = @"ld";
            if (value)
                self.m_Value= [NSString stringWithFormat:@"%ld", [value longValue]];
            else 
                self.m_Value= [NSString stringWithFormat:@"%ld", 0];
        }
        else if (2 == type)
        {
            self.m_Type = @"f";
            if (value)
                self.m_Value= [NSString stringWithFormat:@"%f", [value floatValue]];
            else 
                self.m_Value= [NSString stringWithFormat:@"%f", 0.0];
        }
        else if (3 == type)
        {
            self.m_Type = @"@";
            if (value)
                self.m_Value= [NSString stringWithFormat:@"%@", value];
            else
                self.m_Value= [NSString stringWithString:@""];
        }
        else if (4 == type)
        {
            self.m_Type = @"blob";
            if (value)
                self.m_Value= value;
            else
                self.m_Value= nil;
        }
        else if (5 == type)
        {
            self.m_Type = @"NULL";
            self.m_Value= nil;
        }
        else {;}
        
        self.m_nType  = type;
        self.m_Name   = name;
    }
    
    return self;
}

-(void)dealloc
{
    [m_Name release];
    [m_Type release];
    [m_Value release];
    
    [super dealloc];
}

@end


@implementation CDBManager

static CDBManager	*g_ShareInstanceDB;

@synthesize m_pDB;

+(id)allocWithZone:(NSZone *)zone
{
	@synchronized(self)
	{
		if (nil == g_ShareInstanceDB)
		{
			g_ShareInstanceDB = [super allocWithZone:zone];
			return g_ShareInstanceDB;
		}
	}
	
	return nil;
}

+(CDBManager*)ScheduledShareInstance
{
	if (nil == g_ShareInstanceDB)
	{
		NSString *path = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/Delicious_db.rdb"];
		g_ShareInstanceDB = [[CDBManager alloc] initWithDBName:path];
	}
	
	return g_ShareInstanceDB;
}

+(void)DestoryShareInstance
{
	if (nil != g_ShareInstanceDB)
	{
		[g_ShareInstanceDB release];
	}
	
	g_ShareInstanceDB = nil;
}

-(id)initWithDBName:(NSString*)dbname
{
		
	if (self = [super init])
	{
		m_bIsDBOpened = NO;
		m_strDBName   = [[NSString alloc] initWithString:dbname];
	}
	
	return self; 
}

-(void)dealloc
{
	[m_strDBName release];
	
	[super dealloc];
}

//打开制定数据库
-(BOOL)OpenDatabase
{
	if (!m_bIsDBOpened)
	{
		const char *dbFilePath = [m_strDBName UTF8String];

		if (sqlite3_open(dbFilePath, &m_pDB))
		{
			UIAlertView *unableopen = [[UIAlertView alloc] initWithTitle:@"Error" message:@"Couldn't open db" delegate:nil cancelButtonTitle:@"close" otherButtonTitles:nil];
			[unableopen show];
			[unableopen autorelease];
			
			return NO;
		}
        
        [CUser CreateUserTable:@"Users"];
        //create Best table for user;
        [CProduct CreateProdutTable:@"Best"];
        //create Nearest table for user;
        [CProduct CreateProdutTable:@"Nearest"];
        //create Newest table for user;
        [CProduct CreateProdutTable:@"Newest"];
        [CCommentary CreateCommentaryTable];
        //create Search buffer
        [CProduct CreateProdutTable:@"Search"];
        //create draft table for user
        [CDraft CreateDraftTable:@"Draft"];
        
		m_bIsDBOpened = YES;
	}
	
	return YES;
}

-(void)CreateDB
{
    if (![[NSFileManager defaultManager] fileExistsAtPath:[NSString stringWithFormat:@"%@/Documents/Delicious_db.rdb", NSHomeDirectory()]])
    {
        NSString *respath = [[NSBundle mainBundle] pathForResource:@"Delicious_db" ofType:@"rdb"];
                
        NSError *error = nil;
		if (![[NSFileManager defaultManager] copyItemAtPath:respath toPath:[NSString stringWithFormat:@"%@/Documents/Delicious_db.rdb", NSHomeDirectory()] error:&error])
        {
            UIAlertView *createdbfail = [[UIAlertView alloc] initWithTitle:@"错误" message:@"创建本地数据库失败." delegate:nil cancelButtonTitle:@"知道了" otherButtonTitles:nil];
            [createdbfail show];
            [createdbfail autorelease];
        }
    }

    [self OpenDatabase];
}

//new
-(int)CreateTable:(NSString *)tablename Keys:(NSArray *)keys
{
    int nRet = 1;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
    
    if (dbmanager)
    {
        CKey     *key = nil;
        NSString *tmp = nil;
        NSMutableString *sets = [NSMutableString string];
        [sets appendString:@"("];
        
        int nCnt = [keys count]-1;
        for (int i=0; i<nCnt+1; i++)
        {
            key = [keys objectAtIndex:i];
            
            if (nCnt == i)
            {
                tmp = [[NSString alloc] initWithFormat:@"%@", key.m_Name];
                [sets appendString:tmp];
                [tmp release];
            }
            else 
            {
                tmp = [[NSString alloc] initWithFormat:@" %@,", key.m_Name];
                [sets appendString:tmp];
                [tmp release];
            }
        }
        [sets appendString:@")"];
        
        sqlite3_stmt *dbps = nil;
        NSString      *sql = [NSString stringWithFormat:@"create table IF NOT EXISTS %@ %@", tablename, sets];
        
		const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if ((nRet = sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL)) == SQLITE_OK)
        {
            nRet = sqlite3_step(dbps);
            sqlite3_finalize(dbps);
            
            nRet = (SQLITE_DONE == nRet) ? (0) : (nRet);
        }
    }
    
    return nRet;
}

-(int)RemoveTable:(NSString *)tablename
{
    int nRet = 0;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
	
	if (dbmanager)
    {
        sqlite3_stmt *dbps = nil;
        NSString      *sql = nil;
        
        sql = [[NSString stringWithFormat:@"drop table %@", tablename] retain];
        
        const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if (sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL) == SQLITE_OK)
        {
            if (SQLITE_ROW == sqlite3_step(dbps))
            {
                nRet = sqlite3_column_count(dbps);
                if (nRet > 0)
                {
                    nRet = sqlite3_column_int(dbps, 0);
                }
            }
            sqlite3_finalize(dbps);
        }
        
        [sql release];
    }
    
    return nRet;
}

-(int)ClearTable:(NSString *)tablename
{
    int nRet = 0;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
	
	if (dbmanager)
    {
        sqlite3_stmt *dbps = nil;
        NSString      *sql = nil;
        
        sql = [[NSString stringWithFormat:@"delete from %@", tablename] retain];
        
        const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if (sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL) == SQLITE_OK)
        {
            if (SQLITE_ROW == sqlite3_step(dbps))
            {
                nRet = sqlite3_column_count(dbps);
                if (nRet > 0)
                {
                    nRet = sqlite3_column_int(dbps, 0);
                }
            }
            sqlite3_finalize(dbps);
        }
        
        [sql release];
    }
    
    return nRet;
}

-(int)GetRecordTotal:(NSString *)tablename Condition:(NSString*)condition Key:(NSString*)key
{
    int nRet = 0;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
	
	if (dbmanager)
    {
        sqlite3_stmt *dbps = nil;
        NSString      *sql = nil;
        if (condition)
        {
            sql = [[NSString stringWithFormat:@"select COUNT(%@) from %@ where %@", key, tablename, condition] retain];
        }
        else 
        {
            sql = [[NSString stringWithFormat:@"select COUNT(%@) from %@", key, tablename] retain];
        }
		const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if (sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL) == SQLITE_OK)
        {
            if (SQLITE_ROW == sqlite3_step(dbps))
            {
                nRet = sqlite3_column_count(dbps);
                if (nRet > 0)
                {
                    nRet = sqlite3_column_int(dbps, 0);
                }
            }
            sqlite3_finalize(dbps);
        }
        
        [sql release];
    }
    
    return nRet;
}

-(NSArray*)QueryRecordsFromCursor:(NSString*)tablename Keys:(NSArray*)keys Offset:(int)offset Limit:(int)limit Column:(int)column Condition:(NSString*)condition
{ 
    if (nil == tablename)
    {
        NSLog(@"nill");
    }
    int nRet = 0;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
	
	if (dbmanager)
    {
        NSMutableArray  *rets = [NSMutableArray array];
        NSMutableArray  *sub  = nil;
        sqlite3_stmt    *dbps = nil;
        
        NSMutableString *sql = [[NSMutableString string] retain];
        NSMutableString *sets= [[NSMutableString string] retain];
        NSString *tmp = nil;
        for (int i=0; i<[keys count]; i++)
        {
            int nCnt = [keys count]-1;
            for (int i=0; i<nCnt+1; i++)
            {
                if (nCnt == i)
                {
                    tmp = [[NSString alloc] initWithFormat:@"%@", [keys objectAtIndex:i]];
                    [sets appendString:tmp];
                    [tmp release];
                }
                else 
                {
                    tmp = [[NSString alloc] initWithFormat:@" %@,", [keys objectAtIndex:i]];
                    [sets appendString:tmp];
                    [tmp release];
                }
            }
        }
        if (condition)
        {
            [sql appendFormat:@"select %@ from %@ where %@ limit %d offset %d", sets, tablename, condition, limit, offset];
        }
        else 
        {
            [sql appendFormat:@"select %@ from %@ limit %d offset %d", sets, tablename, limit, offset];
        }
        
		const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if (SQLITE_OK == (nRet = sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL)))
        {
            int nType = 0;
            while (SQLITE_ROW == sqlite3_step(dbps))
            {
                sub  = [[NSMutableArray alloc] initWithCapacity:column];
                for (int i=0; i<column; i++)
                {
                    nType = sqlite3_column_type(dbps, i);
                    //int
                    if (1 == nType)
                    {
                        [sub addObject:[NSNumber numberWithLong:sqlite3_column_int(dbps, i)]];
                    }
                    //float
                    else if (2 == nType)
                    {
                        [sub addObject:[NSNumber numberWithFloat:sqlite3_column_double(dbps, i)]];
                    }
                    //text
                    else if (3 == nType)
                    {
                        [sub addObject:[NSString stringWithUTF8String:(char*)sqlite3_column_text(dbps, i)]];
                    }
                    //blob
                    else if (4 == nType)
                    {
                        [sub addObject:[NSData dataWithBytes:sqlite3_column_blob(dbps, i) length:sqlite3_column_bytes(dbps, i)]];
                    }
                    //null
                    else if (5 == nType)
                    {
                        [sub addObject:[NSNull null]];
                    }
                    else {;}
                }
                
                [rets addObject:sub];
                [sub release];
            }
            sqlite3_finalize(dbps);
            
            [sets release];
            [sql release];
            
            return rets;
        }
        
        [sets release];
        [sql release];
    }
    
    return nil;
}

-(int)InsertNewRecordToTable:(NSString*)tablename Keys:(NSArray*)keys
{
    int nRet = 0;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
	
	if (dbmanager)
    {
        sqlite3_stmt *dbps = nil;
        
        NSMutableString *sql = [NSMutableString string];
        NSMutableString *cols= [[NSMutableString string] retain];
        NSMutableString *vals= [[NSMutableString string] retain];
        NSMutableArray  *blob= [NSMutableArray array];
        NSString *tmp = nil;
        NSString *val = nil;
        CKey     *key = nil;
  
        int nCnt = [keys count]-1;
        for (int i=0; i<nCnt+1; i++)
        {
            key = [keys objectAtIndex:i];
            if (nCnt == i)
            {
                tmp = [[NSString alloc] initWithFormat:@"%@", key.m_Name];
                [cols appendString:tmp];
                [tmp release];
                
                if (3 == key.m_nType)
                {
                    val = [[NSString alloc] initWithFormat:@"\"%@\"", key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else if (1 == key.m_nType)
                {
                    val = [[NSString alloc] initWithFormat:@"%@", key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else if (2 == key.m_nType)
                {
                    val = [[NSString alloc] initWithFormat:@"%@", key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else if (4 == key.m_nType)
                {
                    val = [[NSString alloc] initWithString:@"?"];
                    if (key.m_Value)
                        [blob addObject:key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else {;}
            }
            else 
            {
                tmp = [[NSString alloc] initWithFormat:@" %@,", key.m_Name];
                [cols appendString:tmp];
                [tmp release];
                
                if (3 == key.m_nType)
                {
                    val = [[NSString alloc] initWithFormat:@" \"%@\",", key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else if (1 == key.m_nType)
                {
                    val = [[NSString alloc] initWithFormat:@" %@,", key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else if (2 == key.m_nType)
                {
                    val = [[NSString alloc] initWithFormat:@" %@,", key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else if (4 == key.m_nType)
                {
                    val = [[NSString alloc] initWithString:@" ?,"];
                    if (key.m_Value)
                        [blob addObject:key.m_Value];
                    
                    [vals appendString:val];
                    [val release];
                }
                else {;}
            }
        }
        [sql appendFormat:@"insert into %@ (%@) values (%@)", tablename, cols, vals];
        sql = [NSString stringWithFormat:@"%@", sql];
        
        const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if (SQLITE_OK == (nRet = sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL)))
        {
            NSData *data = nil;
            for (int j=0; j<[blob count]; j++)
            {
                int index = 1;
                data = [blob objectAtIndex:j];
                if (data)
                {
                    nRet = sqlite3_bind_blob(dbps, index++, [data bytes], [data length], nil);
                }
            }
            
            nRet = sqlite3_step(dbps);
            sqlite3_finalize(dbps);
            [vals release];
            [cols release];
            
            nRet = (SQLITE_DONE == nRet) ? (0) : (nRet);
        }
    }
    
    return nRet;
}

-(int)UpdateRecordInfo:(NSString*)tablename Keys:(NSArray*)keys Condition:(NSString*)condition
{
    int nRet = 0;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
	
	if (dbmanager)
    {
        sqlite3_stmt *dbps = nil;
        
        NSMutableString *sql = [NSMutableString string];
        NSMutableString *cols= [NSMutableString string];
        NSMutableArray  *blob= [NSMutableArray array];
        NSString *tmp = nil;
        CKey     *key = nil;
        
        int nCnt = [keys count]-1;
        for (int i=0; i<nCnt+1; i++)
        {
            key = [keys objectAtIndex:i];
            if (nCnt == i)
            {
                tmp = [[NSString alloc] initWithFormat:@"%@=", key.m_Name];
                [cols appendString:tmp];
                [tmp release];
                
                if (3 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithFormat:@"\"%@\"", key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else if (1 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithFormat:@"%@", key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else if (2 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithFormat:@"%@", key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else if (4 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithString:@"?"];
                    if (key.m_Value)
                        [blob addObject:key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else {;}
            }
            else 
            {
                tmp = [[NSString alloc] initWithFormat:@" %@=", key.m_Name];
                [cols appendString:tmp];
                [tmp release];
                
                if (3 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithFormat:@" \"%@\",", key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else if (1 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithFormat:@" %@,", key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else if (2 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithFormat:@" %@,", key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else if (4 == key.m_nType)
                {
                    tmp = [[NSString alloc] initWithString:@" ?,"];
                    [blob addObject:key.m_Value];
                    
                    [cols appendString:tmp];
                    [tmp release];
                }
                else {;}
            }
        }
        if (condition)
        {
            [sql appendFormat:@"update %@ set %@ where %@", tablename, cols, condition];
        }
        else 
        {
            [sql appendFormat:@"update %@ set %@", tablename, cols];
        }
        
        const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if (SQLITE_OK == (nRet = sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL)))
        {
            NSData *data = nil;
            for (int j=0; j<[blob count]; j++)
            {
                int index = 1;
                data = [blob objectAtIndex:j];
                if (data)
                {
                    nRet = sqlite3_bind_blob(dbps, index, [data bytes], [data length], nil);
                }
            }
            
            nRet = sqlite3_step(dbps);
            sqlite3_finalize(dbps);
            
            nRet = (SQLITE_DONE == nRet) ? (0) : (nRet);
        }
    }
    
    return nRet;
}

-(int)DeleteRecord:(NSString*)tablename Condition:(NSString*)condition
{
    int nRet = 1;
    
    CDBManager *dbmanager = [CDBManager ScheduledShareInstance];
	
	if (dbmanager)
    {
        sqlite3_stmt   *dbps = nil;
        NSMutableString *sql = [NSMutableString string];
        [sql appendFormat:@"delete from %@ ", tablename];
        if (nil != condition)
        {
            [sql appendFormat:@"where %@", condition];
        }
        
		const char  *insertps = [sql cStringUsingEncoding:NSUTF8StringEncoding];
        
        if (sqlite3_prepare_v2(dbmanager.m_pDB, insertps, -1, &dbps, NULL) == SQLITE_OK)
        {
            nRet = sqlite3_step(dbps);
            sqlite3_finalize(dbps);
            
            nRet = (SQLITE_DONE == nRet) ? (0) : (nRet);
        }
    }
    
    return nRet;
}

@end
